const mysql = require('mysql')
var operate_sql = {}
var connection = mysql.createConnection({
  host:'127.0.0.1',
  user:'root',
  password: '123456',
  port: '3306',
  database: 'my_test',
  insecureAuth : true
});


// connection.connect()
// var sql = 'SELECT * FROM urls'
// connection.query(sql,function (err, result) {
//   if(err){
//     console.log('[SELECT ERROR] - ',err.message);
//     return;
//   }
//   console.log(result);
// });
// connection.end();


// 操作数据库
operate_sql.query=function(sql,res){
  connection.connect()
  console.log("数据库连接成功");
  connection.query(sql,function (err, result){
    if(err){
      console.log('[SELECT ERROR] - ',err.message);
      return;
    }
    res.end(JSON.stringify(result))
  })
  connection.end();
  console.log("数据库操作完成，断开连接");
}

/**
 * @name 获取列表
 */
operate_sql.list_sql=function(){
  return 'SELECT * FROM urls'
}

/**
 * @name 插入
 * @param tablename 操作表名
 * @param insertArray 即将插入的数据
 */
operate_sql.insert_sql=function(tablename,insertArray){
  let table_name = tablename||'urls'
  let insert_array= insertArray||[{name:'测试名称',url:'测试地址'}]
  let keys = Object.keys(insert_array[0]).join(",")
  let values = []

  insert_array.forEach(ele => {
    values.push("('"+Object.values(ele).join("','")+"')")
  });
  console.log("【即将插入数据】---"+values.join());
  return `INSERT INTO ${table_name} (${keys}) VALUES ${values.join()}`
}

module.exports=operate_sql